const express = require('express');
const router = express.Router()
const pool = require('../utils/pool.js')
const Response = require('../utils/res')

//查询所有服务商信息
router.get('/merchent-query',(req,res,next)=>{
  let sql1 = 'select merchant_info.*,sum(salestotal) zxl,sum(estimate) hp from shop join merchant_info on mname = me_name join sdetails on shid = sid GROUP BY mname limit ?,?;select count(*) total from merchant_info'
  let sql = 'SELECT * from merchant_info;SELECT mname,sum(salestotal) zxl,sum(estimate) hp FROM shop,sdetails WHERE shid=sid GROUP BY mname;SELECT COUNT(*) total from merchant_info'
  pool.query(sql,[req.start,req.pages],(err,r)=>{
    if(err){
      return next(err)
    }
    const data = r[0].map(v=>{
      const {me_name} = v
      r[1].forEach(j=>{
        const {mname,zxl,hp} = j
        if(me_name==mname){
          v.zxl=zxl
          v.hp=hp
        }
      })
    })
    res.send({
      code:200,
      msg:'ok',
      page:req.query.page-0 || 1,
      pagesize:req.pages,
      total:r[2][0].total,
      data:r[0]
    })
  })
})
//按名称模糊查询服务商
router.get('/facil-queryByName',(req,res,next)=>{
  const sql = 'select merchant_info.*,sum(salestotal) zxl,sum(estimate) hp from shop join merchant_info on mname = me_name join sdetails on shid = sid where me_name like ?'
  pool.query(sql,['%'+req.query.mname+'%'],(err,r)=>{
    if(err){
      return next(err)
    }
    res.send({code:200,msg:'ok',data:r})
  })
})

//添加服务商
router.post('/facil-add',(req,res,next)=>{
  const sql = 'insert into merchant_info set ?'
  pool.query(sql,[req.body],(err,r)=>{
    if(err){
      return next(err)
    }
    res.send(Response.ok())
  })
})
//授权服务商账户
router.post('/facil-addAccount',(req,res,next)=>{
  const {muser,mpwd,meid,mphone} = req.body
  pool.query('select * from merchant where muser=?',[muser],(err,r)=>{
    if(err){
      return next(err)
    }
    if(r.length){
      res.send(Response.error(206,'账号已存在'))
    }else{
      let sql = 'insert into merchant set muser=?,mpwd=MD5(?),mphone=?,account=?,access=1;update merchant set ident=(select mid from merchant where muser=?)+"-" where mid=(select mid from merchant where muser=?)'
      pool.query(sql,[muser,mpwd,mphone,meid,muser,muser],(err,r)=>{
        if(err){
          return next(err)
        }
        res.send(Response.ok())
      })
    }
  })
})

//已授权服务商
router.get('/fwsyes',(req,res,next)=>{
  let sql = 'select sql_calc_found_rows * from merchant_info where meid in (select distinct account from merchant) limit ?,?;SELECT FOUND_ROWS() total'
  pool.query(sql,[req.start,req.pages],(err,r)=>{
    if(err){
      return next(err)
    }
    res.send({
        code:200,
        msg:'ok',
        page:req.query.page-0 || 1,
        pagesize:req.pages,
        total:r[1][0].total,
        data:r[0]
      })
  })
})
//未授权账户服务商
router.get('/fwsno',(req,res,next)=>{
  let sql = 'select sql_calc_found_rows * from merchant_info where meid not in (select distinct account from merchant) limit ?,?;SELECT FOUND_ROWS() total'
  pool.query(sql,[req.start,req.pages],(err,r)=>{
    if(err){
      return next(err)
    }
    res.send({
        code:200,
        msg:'ok',
        page:req.query.page-0 || 1,
        pagesize:req.pages,
        total:r[1][0].total,
        data:r[0]
      })
  })
})
//按id删除服务商
router.delete('/delete-fws',(req,res,next)=>{
  const {meid} = req.query
  let sql = "DELETE from sdetails where shid=(SELECT sid from shop WHERE mname IN (SELECT me_name from merchant_info WHERE meid = ?));delete from merchant where account=?;delete from shop where mname in (SELECT me_name from merchant_info WHERE meid = ?);delete from merchant_info where meid=?"
  pool.query(sql,[meid,meid,meid,meid],(err,r)=>{
    if(err){
      return next(err)
    }
    res.send(Response.ok())
  })
})
module.exports = router